package cn.doitedu.sql;

import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;

/**
 * @Author: 深似海
 * @Site: <a href="www.51doit.com">多易教育</a>
 * @QQ: 657270652
 * @Date: 2024/3/2
 * @Desc: 学大数据，上多易教育
 * <p>
 * jdbc连接器使用演示
 * 读取mysql.doit46.students表
 * 求各性别的平均薪资
 * 将结果写到mysql.doit46.avg_salary表
 **/
public class _02_JdbcConnector_Demo {

    public static void main(String[] args) {

        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        StreamTableEnvironment tenv = StreamTableEnvironment.create(env);


        // 建表，映射数据源
        tenv.executeSql(
                "CREATE TABLE students_mysql(   " +
                        "    id     int,        " +
                        "    name   string,     " +
                        "    gender string,     " +
                        "    age    int,        " +
                        "    salary float       " +
                        ") with (               " +
                        "    'connector' = 'jdbc',      " +
                        "    'url' = 'jdbc:mysql://doitedu:3306/doit46', " +
                        "    'table-name' = 'student', " +
                        "    'username' = 'root',       " +
                        "    'password' = 'root'        " +
                        ")");


        // 建表，映射目的地 : mysql.doit46.avg_salary表
        tenv.executeSql(
                "CREATE TABLE avg_salary_mysql ( " +
                        "    gender string,   " +
                        "    salary float,    " +
                        "    primary key (gender)   not enforced  " +
                        ") with (             " +
                        "    'connector' = 'jdbc',      " +
                        "    'url' = 'jdbc:mysql://doitedu:3306/doit46', " +
                        "    'table-name' = 'avg_salary', " +
                        "    'username' = 'root',       " +
                        "    'password' = 'root'       " +
                        ")");

        // 写sql  insert into avg_salary表 select .... from  students_mysql
        tenv.executeSql(
                "insert into avg_salary_mysql  " +
                        "select gender,avg(salary) as salary " +
                        "from students_mysql " +
                        "group by gender ");


    }


}
